package com.red.wood.module.cdn.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.red.wood.common.domain.Page;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.module.cdn.dao.CdnHouseInfoDao;

@Repository
public class CdnHouseInfoDaoImpl implements CdnHouseInfoDao {

	private static final Log log = LogFactory.getLog(CdnHouseInfoDaoImpl.class);
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Map<String, Object> getPageData(Map<String, Object> param) {
		String count = "0";
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		list = filter(listPageDataStatistics(param));
		count = listCountStatistics(param);
		
		Map<String, Object> rs = new HashMap<String, Object>();
		rs.put("totalCount", count);
		rs.put("pageData", list);
		return rs;
	}
	
	@Override
	public List<Map<String, Object>> getDetailInfo(String domainId) {
		String sql = "select t.domainId,t.domain,t.regId,(select count(*) from t_cdn_cdnNetInfo t1 where t1.domainId = t.domainId) as cdnNetNum,t.reportTime,t.modifyTime from t_cdn_customer_service_domain t where domainId = '"+domainId+"' limit 1";
		return jdbcTemplate.queryForList(sql);
	}
	
	public List<Map<String, Object>> listPageDataStatistics(Map<String, Object> param){
		String linkCdnNetId = CommonUtils.valueOf(param.get("linkCdnNetId"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1")) {
			sql.append("select cdnId,houseId,houseName,houseAdd,ipNum,houseType,houseCity,houseProvince,bandWidth,cdnName,cdnProvince,");
			sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType");
			sql.append(" from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince='"+userProvince+"'");
			sql.append(" and house.houseProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
		}else if(StringUtils.equals(userLevel, "2")) {
			sql.append("select cdnId,houseId,houseName,houseAdd,ipNum,houseType,houseCity,houseProvince,bandWidth,cdnName,cdnProvince,");
			sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType");
			sql.append(" from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
		}else {
			sql.append("select cdnId,houseId,houseName,houseAdd,ipNum,houseType,houseCity,houseProvince,bandWidth,cdnName,cdnProvince,");
			sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType");
			sql.append(" from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
			sql.append(" union all ");
			sql.append("select cdnId,houseId,houseName,houseAdd,ipNum,houseType,houseCity,houseProvince,bandWidth,cdnName,cdnProvince,");
			sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType");
			sql.append(" from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince !='"+userProvince+"'");
			sql.append(" and house.houseProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
		}
		/*if(StringUtils.isNotBlank(linkCdnNetId)) {//子网页面下钻过来
			sql = getSql(param);
			sql.append(getQueryConf(param));
		}else {
			sql.append("select cdnId,houseId,houseName,houseAdd,ipNum,houseType,houseCity,houseProvince,bandWidth,cdnName,cdnProvince,");
			sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType");
			sql.append(" from t_cdn_house_statistics house where 1 = 1");
			sql.append(getQueryConfStatistics(param));
		}*/
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public String listCountStatistics(Map<String, Object> param) {
		String linkCdnNetId = CommonUtils.valueOf(param.get("linkCdnNetId"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1")) {
			sql.append("select count(1) count from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince='"+userProvince+"'");
			sql.append(" and house.houseProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
		}else if(StringUtils.equals(userLevel, "2")) {
			sql.append("select count(1) count from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
		}else {
			sql.append("select sum(count) count from(");
			sql.append("select count(1) count from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
			sql.append(" union all ");
			sql.append("select count(1) count from t_cdn_house_statistics house where 1 = 1");
			sql.append(" and house.cdnProvince !='"+userProvince+"'");
			sql.append(" and house.houseProvince='"+userProvince+"'");
			sql.append(getQueryConfStatistics(param));
			sql.append(") t");
		}
		/*if(StringUtils.isNotBlank(linkCdnNetId)) {//子网页面下钻过来
			sql.append("select count(*) count from(");
			sql.append(getSqlCount(param));
			sql.append(getQueryConf(param));
			sql.append(" ) t");
		}else {
			sql.append("select count(1) count from t_cdn_house_statistics house where 1 = 1");
			sql.append(getQueryConfStatistics(param));
		}*/
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public List<Map<String, Object>> listPageData(Map<String, Object> param){
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1") || StringUtils.equals(userLevel, "2")) {
			sql = getSql(param);
			sql.append(getQueryConf(param));
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}else {//高级权限
			sql.append("select * from (");
			sql.append(getSql(param));
			sql.append(" and cdn.province='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSql(param));
			sql.append(" and house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1)");
			sql.append(" and cdn.province !='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}

	public StringBuffer getSql(Map<String, Object> param) {
		String linkCdnNetId = CommonUtils.valueOf(param.get("linkCdnNetId"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		StringBuffer sql = new StringBuffer();
		/*sql.append("select house.houseId,house.houseName,");
		sql.append("       (select count(*) from t_cdn_cdnnet_node_house_ipseg t1 where t1.houseId = house.houseId and t1.cdnId = house.cdnId) as ipNum,");
		sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType,");
		sql.append("       (select area.area_name from t_area area where area.area_code = house.houseCity limit 1) as houseCity,");
		sql.append("       (select sum(t.bandWidth) bandWidth from t_cdn_cdnnet_node_house_gateway t where t.cdnId = house.cdnId and t.houseId = house.houseId) as bandWidth,");
		sql.append("       cdn.cdnName,house.reportTime,house.verifyState,house.modifyTime,house.cdnId,house.cdnNetId,house.nodeId");
		sql.append("  from t_cdn_cdnNet_node_house house,t_cdn_cdndata cdn");
		sql.append(" where house.cdnId = cdn.cdnId");*/
		sql.append("select house.houseId,house.houseName,house.houseAdd,ip.ipNum,gateway.bandWidth,");
		sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType,");
		sql.append("       (select area.area_name from t_area area where area.area_code = house.houseCity limit 1) as houseCity,");
		//sql.append("       (select sum(t.bandWidth) bandWidth from t_cdn_cdnnet_node_house_gateway t where t.cdnId = house.cdnId and t.houseId = house.houseId) as bandWidth,");
		sql.append("       cdn.cdnName,house.cdnId");
		sql.append("  from t_cdn_cdndata cdn");
		if(StringUtils.isNotBlank(linkCdnNetId)) {//子网页面下钻过来
			sql.append(" inner join (select house.cdnId,house.cdnNetId,house.houseId,house.houseName,house.houseAdd,house.houseType,houseCity from t_cdn_cdnNet_node_house house group by house.cdnId,house.cdnNetId,house.houseId,house.houseType,house.houseName,house.houseAdd,house.houseCity) house");
		}else {
			sql.append(" inner join (select house.cdnId,house.houseId,house.houseName,house.houseAdd,house.houseType,houseCity from t_cdn_cdnNet_node_house house group by house.cdnId,house.houseId,house.houseType,house.houseName,house.houseAdd,house.houseCity) house");
		}
		sql.append(" on house.cdnId = cdn.cdnId");
		sql.append(" and cdn.province='"+userProvince+"'");
		sql.append(" left join (select sum(t.bandWidth) bandWidth,t.cdnId,t.houseId from t_cdn_cdnnet_node_house_gateway t group by t.cdnId,t.houseId) gateway");
		sql.append(" on house.cdnId = gateway.cdnId and house.houseId = gateway.houseId");
		sql.append(" left join (select t.cdnId,t.houseId,count(1) ipNum from (select ip.cdnId,ip.houseId,ip.startIp,ip.endIp from t_cdn_cdnNet_node_house_ipSeg ip group by ip.cdnId,ip.houseId,ip.startIp,ip.endIp) t group by t.cdnId,t.houseId) ip");
		sql.append(" on house.cdnId = ip.cdnId and house.houseId = ip.houseId");
		sql.append(" where 1 = 1");
		return sql;
	}
	
	public StringBuffer getSqlCount(Map<String, Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String linkCdnNetId = CommonUtils.valueOf(param.get("linkCdnNetId"));
		StringBuffer sql = new StringBuffer();
		sql.append("select house.houseId,house.houseName,");
		sql.append("       case when house.houseType = '1' then '租用' when house.houseType = '2' then '自建' when house.houseType = '999' then '其他' end as houseType,");
		//sql.append("       (select area.area_name from t_area area where area.area_code = house.houseCity limit 1) as houseCity,");
		//sql.append("       (select sum(t.bandWidth) bandWidth from t_cdn_cdnnet_node_house_gateway t where t.cdnId = house.cdnId and t.houseId = house.houseId) as bandWidth,");
		sql.append("       cdn.cdnName,house.cdnId");
		sql.append("  from t_cdn_cdndata cdn");
		if(StringUtils.isNotBlank(linkCdnNetId)) {//子网页面下钻过来
			sql.append(" inner join (select house.cdnId,house.cdnNetId,house.houseId,house.houseName,house.houseType,houseCity from t_cdn_cdnNet_node_house house group by house.cdnId,house.cdnNetId,house.houseId,house.houseType,house.houseName,house.houseCity) house");
		}else {
			sql.append(" inner join (select house.cdnId,house.houseId,house.houseName,house.houseType,houseCity from t_cdn_cdnNet_node_house house group by house.cdnId,house.houseId,house.houseType,house.houseName,house.houseCity) house");
		}		
		sql.append(" on house.cdnId = cdn.cdnId");
		sql.append(" and cdn.province='"+userProvince+"'");
		sql.append(" where 1 = 1");
		return sql;
	}
	
	public String listCount(Map<String, Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "3")) {
			sql.append("select count(*) count from (");
			sql.append(getSqlCount(param));
			sql.append(" and cdn.province='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSqlCount(param));
			sql.append(" and house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1)");
			sql.append(" and cdn.province !='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}else {
			sql.append("select count(*) count from(");
			sql.append(getSqlCount(param));
			sql.append(getQueryConf(param));
			sql.append(" ) t");
		}
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public StringBuffer getQueryConfStatistics(Map<String,Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String linkCdnNetId = CommonUtils.valueOf(param.get("linkCdnNetId"));
		String houseName = CommonUtils.valueOf(param.get("houseName"));
		String cdnName = CommonUtils.valueOf(param.get("cdnName"));
		String houseType = CommonUtils.valueOf(param.get("houseType"));
		String cdnId = CommonUtils.valueOf(param.get("cdnId"));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer queryConf = new StringBuffer();
		
		if(StringUtils.isNotBlank(linkCdnNetId)) {
			queryConf.append(" and house.cdnNetId='"+linkCdnNetId+"'");
		}
		if(StringUtils.isNotBlank(cdnId)) {
			queryConf.append(" and house.cdnId='"+cdnId+"'");
		}
		if(StringUtils.isNotBlank(houseName)) {
			queryConf.append(" and house.houseName like '%"+houseName+"%'");
		}
		if(StringUtils.isNotBlank(cdnName)) {
			queryConf.append(" and house.cdnName like '%"+cdnName+"%'");
		}
		if(!StringUtils.equals(houseType, "all")) {
			queryConf.append(" and house.houseType='"+houseType+"'");
		}
		return queryConf;
	}
	
	public StringBuffer getQueryConf(Map<String,Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String linkCdnNetId = CommonUtils.valueOf(param.get("linkCdnNetId"));
		String houseName = CommonUtils.valueOf(param.get("houseName"));
		String cdnName = CommonUtils.valueOf(param.get("cdnName"));
		String houseType = CommonUtils.valueOf(param.get("houseType"));
		String reportTimeStart = CommonUtils.valueOf(param.get("reportTimeStart"));
		String reportTimeEnd = CommonUtils.valueOf(param.get("reportTimeEnd"));
		String modifyTimeStart = CommonUtils.valueOf(param.get("modifyTimeStart"));
		String modifyTimeEnd = CommonUtils.valueOf(param.get("modifyTimeEnd"));
		String cdnId = CommonUtils.valueOf(param.get("cdnId"));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer queryConf = new StringBuffer();
		
		/*if(!StringUtils.equals(userLevel, "3")) {
			queryConf.append(" and cdn.province='"+userProvince+"'");
			if(StringUtils.equals(userLevel, "1")) {//初级权限
				queryConf.append(" and house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1)");
			}else if(StringUtils.equals(userLevel, "2")) {//中级权限
				//中级权限企业下资源不需要限制
			}
		}*/
		if(StringUtils.isNotBlank(linkCdnNetId)) {
			queryConf.append(" and house.cdnNetId='"+linkCdnNetId+"'");
		}
		if(StringUtils.isNotBlank(cdnId)) {
			queryConf.append(" and house.cdnId='"+cdnId+"'");
		}
		if(StringUtils.isNotBlank(houseName)) {
			queryConf.append(" and house.houseName like '%"+houseName+"%'");
		}
		if(StringUtils.isNotBlank(cdnName)) {
			queryConf.append(" and cdn.cdnName like '%"+cdnName+"%'");
		}
		if(!StringUtils.equals(houseType, "all")) {
			queryConf.append(" and house.houseType='"+houseType+"'");
		}
		/*if(StringUtils.isNotBlank(reportTimeStart)) {
			queryConf.append(" and house.reportTime >='"+reportTimeStart+"'");
		}
		if(StringUtils.isNotBlank(reportTimeEnd)) {
			queryConf.append(" and house.reportTime <='"+reportTimeEnd+"'");
		}
		if(StringUtils.isNotBlank(modifyTimeStart)) {
			queryConf.append(" and house.modifyTime >='"+modifyTimeStart+"'");
		}
		if(StringUtils.isNotBlank(modifyTimeEnd)) {
			queryConf.append(" and house.modifyTime <='"+modifyTimeEnd+"'");
		}*/
		return queryConf;
	}
	public List<Map<String, Object>> filter(List<Map<String, Object>> list){
		if(list != null && list.size() > 0) {
			for(Map<String,Object> map : list) {
				map.put("ipNum",CommonUtils.filteNull(map.get("ipNum")));
				map.put("bandWidth",CommonUtils.filteNull(map.get("bandWidth")));
			}
		}
		return list;
	}
}
